#!/usr/bin/env python
# -*- coding:utf-8 -*-
import configparser
import os
import sys

__dir__ = os.path.dirname(os.path.abspath(__file__))
sys.path.append(__dir__)
sys.path.append(os.path.abspath(os.path.join(__dir__, '../..')))
f = r'E:\zentao_notify\vpTransdoor\setting.ini'
config_file = configparser.RawConfigParser()
config_file.read(f, encoding='UTF8')

obj_col = []
# #####################################################################################################################
# STD AREA
# #####################################################################################################################
# 数据列方法名称
std_col = [
    "SA_0", "SA_1", "SA_2", "SA_3", "SA_4", "SA_5", "Total_min_SA", "Running_SA", "SA_too_low", "Year", "Rebate",
    "EGC_premium", "Cumulative_premium", "Annual_withdrawal", "Remain_", "Balance", "EGC_premium", "EGC_CV",
    "Adjusted_EGC_CV", "A_EGC_CV_1", "A_EGC_CV_2", "A_EGC_CV_3", "A_EGC_CV_4", "A_EGC_CV_5", "Total_A_EGC_CV",
    "re_adjusted_CV", "Balance_before_withdraw", "Retired", "Income", "Inflation_factor", "Inflated_income",
    "Withdrawal", "Cashflow"
]
# 数据列的excel坐标
std_col2 = [
    "K60", "L60", "M60", "N60", "O60", "P60", "Q60", "R60", "S60", "T60", "U60", "V60", "W60", "X60", "Y60", "Z60",
    "AA60", "AB60", "AC60", "AD60", "AE60", "AF60", "AG60", "AH60", "AI60", "AJ60", "AK60", "AL60", "AM60", "AN60",
    "AO60", "AP60", "AQ60"
]
# 数据名称
std_col3 = [
    "SA 0", "SA 1", "SA 2", "SA 3", "SA 4", "SA 5", "Total min SA", "Running SA", "SA too low", "Year", "Rebate",
    "EGC premium", "Cumulative premium", "Annual withdrawal ", "Remain ", "Balance", "EGC premium", "EGC CV",
    "Adjusted EGC CV", "A. EGC CV 1", "A. EGC CV 2", "A. EGC CV 3", "A. EGC CV 4", "A. EGC CV 5", "Total A. EGC CV",
    "re_adjusted CV", "Balance before withdraw", "Retired", "Income", "Inflation factor", "Inflated income",
    "Withdrawal", "Cashflow"
]
# 数据使用的EXCEL公式
std_col4 = [
    "=$C$20", "=IF($C$7>0,IF(($T62-$C$6+1)>0,$C$20*(1+$C$8)^1,0),0)",
    "=IF($C$7>1,IF(($T62-$C$6*2+1)>0,$C$20*(1+$C$8)^2,0),0)", "=IF($C$7>2,IF(($T62-$C$6*3+1)>0,$C$20*(1+$C$8)^3,0),0)",
    "=IF($C$7>3,IF(($T62-$C$6*4+1)>0,$C$20*(1+$C$8)^4,0),0)", "=IF($C$7>4,IF(($T62-$C$6*5+1)>0,$C$20*(1+$C$8)^5,0),0)",
    "=(ROUNDDOWN(MIN(T62,$C$6*(1+$C$7)-1)/$C$6,0)+1)*$C$45", "=SUM($K62:$P62)*$Y61", "=$R62<$Q62", "=B62-$C$3", "0",
    "=IF(B62<102,IF(AND($C$18,A62<$C$6),IF(A62=0,AA62*$C$6-$C$19,0),AA62)*$C$33-U62,0)", "=W61+V62",
    "=IF(AJ62>0,(E62/$C$33)/AJ62,0)", "=Y61-X62", "=Y62*AJ62",
    "=IF(B62-$C$3<($C$6*($C$7+1)),$C$40*(1+$C$8)^ROUNDDOWN((B62-$C$3)/$C$6,0),0)",
    "=VLOOKUP(B62-$C$3+1,data4!$A$4:$FL$124,$C$38,TRUE)*$C$39", "=AB62",
    "=IF($C$7>0,IF(($T62-$C$6+1)>0,VLOOKUP($B62-$C$6,$B$62:$AC$162,$C$42,TRUE)*(1+$C$8)^1,0),0)",
    "=IF($C$7>1,IF(($T62-$C$6*2+1)>0,VLOOKUP($B62-$C$6*2,$B$62:$AC$162,$C$42,TRUE)*(1+$C$8)^2,0),0)",
    "=IF($C$7>2,IF(($T62-$C$6*3+1)>0,VLOOKUP($B62-$C$6*3,$B$62:$AC$162,$C$42,TRUE)*(1+$C$8)^3,0),0)",
    "=IF($C$7>3,IF(($T62-$C$6*4+1)>0,VLOOKUP($B62-$C$6*4,$B$62:$AC$162,$C$42,TRUE)*(1+$C$8)^4,0),0)",
    "=IF($C$7>4,IF(($T62-$C$6*5+1)>0,VLOOKUP($B62-$C$6*5,$B$62:$AC$162,$C$42,TRUE)*(1+$C$8)^5,0),0)", "=SUM(AC62:AH62)",
    "=IF($S62,$AJ61*(1+Notes!$B$3),$AI62)", "=AJ62*Y61", "=(B62>=$F$5)",
    "=IF(AL62,IF($F$8>0,AND($B62<$F$3,($B62-$F$5)<$F$8),$B62<$F$3),FALSE)", "=IF(AL62,(1+$F$7)^(B62-$F$5),1)",
    "=IF($AM62,$F$6/$C$33*$AN62,0)", "=MIN(AO62+D62/$C$33,AK62)", "=IF($F$3>$C$3,IF($B62<$F$3,$E62-$C62,$E62+$G62),0)"
]
std_col5 = [
    "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "FALSE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE",
    "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "FALSE",
    "FALSE", "TRUE", "TRUE", "TRUE", "TRUE"
]
# #####################################################################################################################
# TOP AREA
# #####################################################################################################################
top_col = [
    "lumsum", "lumpsum_discount", "Saving_Sum_Assured", "SA", "1Y_discount", "3Y_discount", "5Y_discount",
    "8Y_discount", "12Y_discount", "EGC_discount", "discount_adjusted_premium", "discount_adjusted_SA", "Chi",
    "Exchange_rate", "Display_rate", "Underlying_currency", "Display_currenty", "EGC_payment_term_index",
    "EGC_currency_index", "EGC_sum_assured_index", "EGC_SA_factor", "EGC_annual_premium", "EGC_annual_premium_HKD",
    "Adjusted_EGC_CV_col", "Total_EGC_premium", "SA_minimum", "low_SA_message", "max_withdrawal_age", "surrender_value",
    "seed_value", "max_withdrawal"

]
# 数据列的excel坐标
top_col2 = [
    "E18", "E19", "E20", "E21", "E22", "E23", "E24", "E25", "E26", "E27", "E28", "E29", "E30", "E31", "E32", "E33",
    "E34", "E35", "E36", "E37", "E38", "E39", "E40", "E41", "E42", "E43", "E44", "E45", "E46", "E47", "E48", "E49",
    "E50", "E51", "E52", "E53"
]
# 数据名称
top_col3 = [
    "lumsum", "lumpsum discount", "Saving Sum Assured", "SA", "1Y discount", "3Y discount", "5Y discount",
    "8Y discount", "12Y discount", "EGC discount", "discount adjusted premium", "discount adjusted SA", "Chi",
    "Exchange rate", "Display rate", "Underlying currency", "Display currenty", "EGC payment term index",
    "EGC currency index", "EGC sum assured index", "EGC SA factor", "EGC annual premium", "EGC annual premium_HKD",
    "Adjusted EGC CV col", "Total EGC premium", "SA minimum", "low SA message", "max withdrawal age", "surrender value",
    "seed value", "max withdrawal"
]
# 数据使用的EXCEL公式
top_col4 = [
    "=AND(LOWER($F$11)='y',$C$6=5,OR(LOWER($C$10)='hkd',LOWER($C$10)='usd',LOWER($C$10)='hkd-u',LOWER($C$10)='rmb-u'))",
    "=IF($C$18,IF(LOWER($C$10)='hkd',35.293%,37.01%)*$C$39,0)", "=$C$29",
    "=VLOOKUP('SA',[数据参考文件.xlsx]data4!$A$2:$FL$102,$C$37,FALSE)*($C$5/$C$32/VLOOKUP('ANB',[数据参考文件.xlsx]data4!$A$2:$FL$102,$C$37,FALSE))",
    "=IF($C$21/$C$31>=150000,30.2,IF($C$21/$C$31>=100000,23.6,IF($C$21/$C$31>=50000,19.8,IF($C$21/$C$31>=35000,17,IF($C$21/$C$31>=20000,11.4,0)))))-30.2",
    "=IF($C$21/$C$31>=150000,10.4,IF($C$21/$C$31>=100000,8,IF($C$21/$C$31>=50000,6.8,IF($C$21/$C$31>=35000,5.8,IF($C$21/$C$31>=20000,3.8,0)))))-10.4",
    "=IF($C$21/$C$31>=150000,6.4,IF($C$21/$C$31>=100000,5,IF($C$21/$C$31>=50000,4.2,IF($C$21/$C$31>=35000,3.6,IF($C$21/$C$31>=20000,2.4,0)))))-6.4",
    "=IF($C$21/$C$31>=150000,4,IF($C$21/$C$31>=100000,3.2,IF($C$21/$C$31>=50000,2.6,IF($C$21/$C$31>=35000,2.2,IF($C$21/$C$31>=20000,1.6,0)))))-4",
    "=IF($C$21/$C$31>=150000,3,IF($C$21/$C$31>=100000,2.4,IF($C$21/$C$31>=50000,2,IF($C$21/$C$31>=35000,1.6,IF($C$21/$C$31>=20000,1,0)))))-3",
    "0",
    "=(VLOOKUP('ANB',[数据参考文件.xlsx]data4!$A$2:$FL$102,$C$37,FALSE)*1000/VLOOKUP('SA',[数据参考文件.xlsx]data4!$A$2:$FL$102,$C$37,FALSE)-$C$27)*$C10/1000",
    "=ROUNDUP($C$5/($C$28*$C$32)*$C$21/100,0)*100", "=IF(LOWER(D150)='chi','Yes','No')", "=IF(LOWER($C$10)='hkd',8,1)",
    "=IF(LOWER($C$10)='rmb-u',[数据参考文件.xlsx]Notes!$B$2,IF(LOWER($C$10)='hkd-u',7.8,1))",
    "=IF(LOWER($C$10)='hkd','(HKD)',IF(LOWER($C$10)='rmb','(RMB)',IF(LOWER($C$10)='aud','(AUD)',IF(LOWER($C$10)='cad','(CAD)',IF(LOWER($C$10)='gbp','(GBP)','(USD)')))))",
    "=IF(OR(LOWER($C$10)='rmb-u',LOWER($C$10)='rmb'),'(RMB)',IF(OR(LOWER($C$10)='hkd-u',LOWER($C$10)='hkd'),'(HKD)',IF(LOWER($C$10)='aud','(AUD)',IF(LOWER($C$10)='cad','(CAD)',IF(LOWER($C$10)='gbp','(GBP)','(USD)')))))",
    "=IF($C$6=3,145,151)",
    "=IF(LOWER($C$10)='hkd',$C$35+0,IF(LOWER($C$10)='rmb',$C$35+2,IF(LOWER($C$10)='aud',$C$35+3,IF(LOWER($C$10)='cad',$C$35+4,IF(LOWER($C$10)='gbp',$C$35+5,$C$35+1)))))",
    "=E25", "=$C$20/VLOOKUP('SA',[数据参考文件.xlsx]data4!$A$2:$FL$102,$C$37,FALSE)",
    "=(VLOOKUP('ANB',[数据参考文件.xlsx]data4!$A$2:$FL$102,$C$37,FALSE)*1000/VLOOKUP('SA',[数据参考文件.xlsx]data4!$A$2:$FL$102,$C$37,FALSE)-$C$27)*$C$20/1000",
    "=IF(LOWER($C$10)='hkd',1,IF(LOWER($C$10)='rmb',1.2,IF(LOWER($C$10)='aud',6,IF(LOWER($C$10)='cad',6.5,IF(LOWER($C$10)='gbp',11,7.8)))))*E28",
    "=COLUMN(AE45)-COLUMN(D45)+1", "=SUM(X47:X145)",
    "=IF(LOWER($C$10)='hkd',80000,IF(LOWER($C$10)='rmb',60000,IF(LOWER($C$10)='aud',12000,IF(LOWER($C$10)='cad',12000,IF(LOWER($C$10)='gbp',6000,10000)))))",
    "=IF($C$30='Yes','名義金額過低','Notional Amount Too Low')", "=IF($F$8=0,$F$3,$F$5+$F$8-1)",
    "=VLOOKUP($C$45,D47:AK147,COLUMN(AK147)-COLUMN(D47)+1,FALSE)", "=$C$46/(IF($F$8=0,$F$3-$F$5+1,$F$8))",
    "=ROUNDDOWN($DK$46*$C$32/1000,0)*1000"
]
top_col5 = [
    "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "FALSE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE",
    "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "FALSE",
    "FALSE", "TRUE", "TRUE", "TRUE", "TRUE"
]
START = 11
END = 43
DECIMAL_NUM = 2

CALSS_NAME = "Egc"


def makeTabCalStd():
    __dir__ = os.path.dirname(os.path.abspath(__file__))
    sys.path.append(__dir__)
    sys.path.append(os.path.abspath(os.path.join(__dir__, '../..')))
    arry_method = []
    arry_call = []
    try:
        print("METHOD:" + "#" * 100)
        for _idx in range(len(std_col)):
            arry_method.append(" " * 4 + f"# 计算 {std_col3[_idx]}")
            arry_method.append(" " * 4 + f"# return: {std_col3[_idx]} {std_col2[_idx]}: {std_col3[_idx]}")
            arry_method.append(" " * 4 + f"# Params:")
            arry_method.append(" " * 4 + f"def get_{std_col[_idx]}(self,):")
            arry_method.append(" " * 8 + f"# 参考计算公式")
            arry_method.append(" " * 8 + f"# {std_col4[_idx]}")
            arry_method.append(" " * 8 + f"return 0")
            arry_method.append("\n")
        print("NAMING:" + "#" * 100)
        for _idx in range(END - START):
            if std_col5[_idx] == "TRUE":
                print(" " * 20 + f"one_{std_col[_idx]}_res = 0")
            else:
                print(" " * 20 + f"one_{std_col[_idx]}_res = ''")
        print("CALLING:" + "#" * 100)
        for _idx in range(END - START):
            arry_call.append(" " * 20 + f"# Col {_idx + START} 计算 {std_col3[_idx]}")
            arry_call.append(" " * 20 + f"one_{std_col[_idx]} = calStd.get_{std_col[_idx]}()")
            if std_col5[_idx] == "TRUE":
                arry_call.append(
                    " " * 20 + f"single_row_data[\"one_{std_col[_idx]}_res\"] = round(one_{std_col[_idx]}, {DECIMAL_NUM})")
            else:
                arry_call.append(" " * 20 + f"single_row_data[\"one_{std_col[_idx]}_res\"] = one_{std_col[_idx]}")
        print("END:" + "#" * 100)

        str_file_path = config_file["excelobj"]["basePath"]
        _content = ''
        with open(str_file_path, 'r', encoding='utf8') as _f:
            _content = _f.read()
            _content = str(_content).replace("###AclassName###", CALSS_NAME)
            _content = str(_content).replace("###className###", str(CALSS_NAME).lower())
            _content = str(_content).replace("## METHOD ##", "\n".join(arry_method))
            _content = str(_content).replace("## CALLING ##", "\n".join(arry_call))
            print("Make file:\n" + _content)
            _f.close()
        str_out_file_path = config_file["excelobj"][
                                "outPath"] + f"\\{str(CALSS_NAME).upper()}\\" + CALSS_NAME + "TabCalStd.py"
        with open(str_out_file_path, 'a', encoding='utf8') as _f:
            _f.write(_content)
            _f.write('\n')
            _f.close()
    except Exception as e:
        print(e)


def makeTabTop():
    arry_method = []
    arry_naming = []
    try:
        p_path = config_file["excelobj"]["outPath"] + f"\\{str(CALSS_NAME).upper()}\\"
        str_out_file_path = p_path + CALSS_NAME + "TabTop.py"
        if not os.path.exists(p_path):
            os.makedirs(p_path)
        print("METHOD:" + "#" * 100)
        for _idx in range(len(top_col)):
            arry_method.append(" " * 4 + f"# 计算 {top_col3[_idx]}")
            arry_method.append(" " * 4 + f"# Return: {top_col3[_idx]} {top_col2[_idx]}: {top_col3[_idx]}")
            arry_method.append(" " * 4 + f"# Params: ")
            arry_method.append(" " * 4 + f"def get_{top_col[_idx]}(self,):")
            arry_method.append(" " * 8 + f"# 参考计算公式")
            arry_method.append(" " * 8 + f"# {top_col4[_idx]}")
            arry_method.append(" " * 8 + f"return 0")
            arry_method.append("\n")
        print("NAMING:" + "#" * 100)
        for _idx in range(len(top_col)):
            if top_col5[_idx] == "TRUE":
                arry_naming.append(" " * 8 + f"\"{top_col[_idx]}\" : 0,")
            else:
                arry_naming.append(" " * 8 + f"\"{top_col[_idx]}\" : '',")

        print("END:" + "#" * 100)

        str_file_path = config_file["excelobj"]["topPath"]
        _content = ''
        with open(str_file_path, 'r', encoding='utf8') as _f:
            _content = _f.read()
            _content = str(_content).replace("###AclassName###", CALSS_NAME)
            _content = str(_content).replace("## METHOD ##", "\n".join(arry_method))
            _content = str(_content).replace("## NAMING ##", "\n".join(arry_naming))
            print("Make file:\n" + _content)
            _f.close()
        with open(str_out_file_path, 'a', encoding='utf8') as _f:
            _f.write(_content)
            _f.write('\n')
            _f.close()
    except Exception as e:
        print(e)


if __name__ == '__main__':
    makeTabTop()
    makeTabCalStd()
